//
//  KVTable.swift
//  GerritHermes
//
//  Created by J.Zhou on 2020/2/6.
//  Copyright © 2020 J.Zhou. All rights reserved.
//

import Foundation
import SQLite

protocol KVTable {
    static var tableName: String { get }
    var keyId: String { get }
    var relatedKeyId: String? { get }
}

extension KVTable {
    var relatedKeyId: String? {
        return nil
    }
}

private extension KVTable {
    static var db: Connection { KVTableHelper.shared.db }
    static var idColumn: Expression<Int> { Expression<Int>("id") }
    static var keyColumn: Expression<String> { Expression<String>("key") }
    static var relatedKeyColumn: Expression<String?> { Expression<String?>("relatedKey") }
    static var dataColumn: Expression<Blob> { Expression<Blob>("data") }
    static var table: Table { Table(tableName) }
}

private extension KVTable where Self: Codable {
    static var encoder: JSONEncoder { return KVTableHelper.shared.encoder }
    static var decoder: JSONDecoder { return KVTableHelper.shared.decoder }
}

extension KVTable {
    static func inTransaction(_ block: () throws -> Void) throws {
        try db.transaction {
            try block()
        }
    }
}

extension KVTable where Self: Codable {

    static func createIfNotExsists() throws {
        try db.run(table.create(ifNotExists: true) { t in
            t.column(idColumn, primaryKey: .autoincrement)
            t.column(keyColumn, unique: true)
            t.column(relatedKeyColumn)
            t.column(dataColumn)
        })
    }

    static func insertOrReplace(_ value: Self) throws {
        let data = try encoder.encode(value)
        if let relatedKeyId = value.relatedKeyId {
            try db.run(table.insert(or: .replace, keyColumn <- value.keyId, relatedKeyColumn <- relatedKeyId, dataColumn <- data))
        } else {
            try db.run(table.insert(or: .replace, keyColumn <- value.keyId, dataColumn <- data))
        }
    }

    static func insertOrReplace(_ value: Self, relatedKey: String) throws {
        let data = try encoder.encode(value)
        try db.run(table.insert(or: .replace, keyColumn <- value.keyId, relatedKeyColumn <- relatedKey, dataColumn <- data))
    }

    static func insertOrReplace(_ values: [Self]) throws {
        try db.transaction {
            try values.forEach(insertOrReplace)
        }
    }

    static func insertOrReplace(_ values: [Self], relatedKey: String) throws {
        try db.transaction {
            try values.forEach {
                try insertOrReplace($0, relatedKey: relatedKey)
            }
        }
    }

    static func insertOrReplace(_ value: Self, _ more: Self...) throws {
        try db.transaction {
            try ([value] + more).forEach(insertOrReplace)
        }
    }

    static func delete(_ value: Self) throws {
        let table = self.table.filter(keyColumn == value.keyId)
        try db.run(table.delete())
    }

    static func delete(_ values: [Self]) throws {
        let table = self.table.filter(values.map({ $0.keyId }).contains(keyColumn))
        try db.run(table.delete())
    }

    static func delete(_ value: Self, _ more: Self...) throws {
        let table = self.table.filter(([value] + more).map({ $0.keyId }).contains(keyColumn))
        try db.run(table.delete())
    }

    static func deleteAll() throws {
        try db.run(table.delete())
    }

    static func delete(related: String) throws {
        let table = self.table.filter(relatedKeyColumn == related)
        try db.run(table.delete())
    }

    static func select(_ id: String) throws -> Self? {
        let table = self.table.filter(keyColumn == id)
        if let row = try db.pluck(table) {
            return try? decoder.decode(Self.self, from: row[dataColumn])
        } else {
            return nil
        }
    }

    static func select(_ id: Self) throws -> Self? {
        return try select(id.keyId)
    }

    static func select(_ ids: [String]) throws -> [Self] {
        let table = self.table.filter(ids.contains(keyColumn))
        return Array(try db.prepare(table)).compactMap {
            try? decoder.decode(Self.self, from: $0[dataColumn])
        }
    }

    static func select(_ ids: [Self]) throws -> [Self] {
        return try select(ids.map { $0.keyId })
    }

    static func select(_ id: String, _ more: String...) throws -> [Self] {
        let table = self.table.filter(([id] + more).contains(keyColumn))
        return Array(try db.prepare(table)).compactMap {
            try? decoder.decode(Self.self, from: $0[dataColumn])
        }
    }

    static func select(_ id: Self, _ more: Self...) throws -> [Self] {
        return try select(([id] + more).map { $0.keyId })
    }

    static func select(notIn ids: [String]) throws -> [Self] {
        let table = self.table.filter(!ids.contains(keyColumn))
        return Array(try db.prepare(table)).compactMap {
            try? decoder.decode(Self.self, from: $0[dataColumn])
        }
    }

    static func select(notIn values: [Self]) throws -> [Self] {
        return try select(notIn: values.map({ $0.keyId }))
    }

    static func selectAll() throws -> [Self] {
        return Array(try db.prepare(table)).compactMap {
            try? decoder.decode(Self.self, from: $0[dataColumn])
        }
    }

    static func select(related: String) throws -> [Self] {
        let table = self.table.filter(relatedKeyColumn == related)
        return Array(try db.prepare(table)).compactMap {
            try? decoder.decode(Self.self, from: $0[dataColumn])
        }
    }
}
